﻿namespace etracks.Web.Controllers
{
    using etracks.EntityModels;
    using etracks.Helpers;
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Linq;
    using System.Web;
    using System.Web.Mvc;
    using System.Web.Mvc.Ajax;
    using System.Data.Linq;
    using System.Data.Common;

	[Authorize]
	public class StudentController : BaseController
	{
		public ActionResult Index ()
		{
            StudentIndexViewModel vm = new StudentIndexViewModel();
            vm.Students = StudentController.GetStudentCount();

			return View (vm);
		}

        //[HttpGet]
        public ActionResult List()
        {
            StudentListViewModel vm = new StudentListViewModel();

            vm.StudentList = StudentController.GetStudentList();

            return this.List(vm);
        }

        private ActionResult List(StudentListViewModel vm)
        {
            return View(vm);
        }

        public ActionResult Search()
        {
            return RedirectToAction("List");
        }

        [HttpPost]
        public ActionResult Search(string search_term)
        {
            StudentSearchViewModel vm = new StudentSearchViewModel();

            vm.Search_Term = search_term;

            vm.StudentList = StudentController.StudentSearchResultsList(search_term);

            return View(vm);
        }

        public ActionResult Registration()
        {
            return View(new etracks.Web.StudentRegistrationViewModel());
        }

        [HttpPost]
        public ActionResult Registration(StudentRegistrationViewModel vm)
        {
            // Validate vm
            if (string.IsNullOrEmpty(vm.Full_Name))
                ModelState.AddModelError("Full_Name", "Cannot be empty.");
            if (string.IsNullOrEmpty(vm.Display_Name))
                ModelState.AddModelError("Display_Name", "Cannot be empty.");
            if (string.IsNullOrEmpty(vm.Nationality_Id))
                ModelState.AddModelError("Nationality_Id", "Cannot be empty.");

            if (ModelState.IsValid)
            {
                // Add student into list database records.
                string new_id = RegisterStudent(vm.Full_Name, vm.Display_Name, vm.Nationality_Id);
            }

            return View(vm);
        }

        ////////////////////////////////////////////////////////////////////////////////
        // Data retrieval hack functions
        private static int GetStudentCount()
        {
            int result = 0;
            object db_result = PostgresHelper.GetObject("SELECT COUNT(*) FROM student;");

            if (db_result != null)
                int.TryParse(db_result.ToString(), out result);
            
            return result;
        }

        private static List<Student> GetStudentList()
        {
            List<Student> result = new List<Student>();

            DataTable dt = PostgresHelper.GetDataTable("SELECT * FROM student ORDER BY id;");
            if (dt != null)
            {
                result = dt.AsEnumerable().Select(n => new Student
                {
                    Id = n.Field<string>("id"),
                    Full_Name = n.Field<string>("full_name"),
                    Display_Name = n.Field<string>("display_name"),
                    Nationality_Id = n.Field<string>("nationality_id")
                }).ToList();
            }

            return result;
        }

        private static List<Student> StudentSearchResultsList(string search_term)
        {
            List<Student> result = new List<Student>();

            Dictionary<string, object> parameters = new Dictionary<string, object>();
            
            parameters.Add("search_term", string.Format("%{0}%", search_term));

            DataTable dt = PostgresHelper.GetDataTable(@"SELECT * FROM student 
WHERE 
lower(id) LIKE lower(:search_term)
OR lower(full_name) LIKE lower(:search_term)
OR lower(display_name) LIKE lower(:search_term)
OR lower(nationality_id) LIKE lower(:search_term)
ORDER BY id;", parameters);

            if (dt != null)
            {
                result = dt.AsEnumerable().Select(n => new Student
                {
                    Id = n.Field<string>("id"),
                    Full_Name = n.Field<string>("full_name"),
                    Display_Name = n.Field<string>("display_name"),
                    Nationality_Id = n.Field<string>("nationality_id")
                }).ToList();
            }

            return result;
        }

        private static string RegisterStudent(string full_name, string display_name, string nationality_id)
        {
            Dictionary<string, object> parameters = new Dictionary<string, object>();
            parameters.Add("full_name", full_name);
            parameters.Add("display_name", display_name);
            parameters.Add("nationality_id", nationality_id);

            string sql = "INSERT INTO student (full_name, display_name, nationality_id) VALUES (:full_name, :display_name, :nationality_id) RETURNING id;";

            object db_result = PostgresHelper.GetObject(sql, parameters);

            if (db_result != null)
                return db_result.ToString();
            else
                return null;

        }
	}
}